package org.example.mock;

import lombok.extern.slf4j.Slf4j;
import org.example.bean.MovieTypeAvg;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * 生成电影类型统计数据并存储到数据库
 */
@Slf4j
public class MovieTypeAvgGenerator {
    private static final String DB_URL = "jdbc:mysql://192.168.10.102/changchengfilm?requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "000000";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // 初始化电影类型表（确保外键约束通过）
            initMovieTypesTable(conn);

            log.info("开始生成电影类型统计数据");
            List<MovieTypeAvg> result = generateData();
            log.info("生成完成，数据如下：");
            result.forEach(data -> log.info(data.toString()));

            log.info("开始将数据存入数据库");
            saveToDatabase(result);
            log.info("数据存储完成");
        } catch (SQLException e) {
            log.error("数据库操作异常", e);
        }
    }

    /**
     * 生成电影类型统计数据（确保type_id与电影类型对应）
     */
    private static List<MovieTypeAvg> generateData() {
        List<MovieTypeAvg> list = new ArrayList<>();
        Random random = new Random();

        // 定义十种电影类型及其对应的type_id
        Map<Integer, String> movieTypes = new HashMap<>();
        movieTypes.put(1, "动作");
        movieTypes.put(2, "喜剧");
        movieTypes.put(3, "爱情");
        movieTypes.put(4, "科幻");
        movieTypes.put(5, "悬疑");
        movieTypes.put(6, "惊悚");
        movieTypes.put(7, "犯罪");
        movieTypes.put(8, "动画");
        movieTypes.put(9, "奇幻");
        movieTypes.put(10, "冒险");

        // 为每种类型生成统计数据
        for (Map.Entry<Integer, String> entry : movieTypes.entrySet()) {
            int typeId = entry.getKey();
            String typeName = entry.getValue();

            // 生成随机评分 (0-10分，保留1位小数)
            BigDecimal avgScore = BigDecimal.valueOf(random.nextDouble() * 10)
                    .setScale(1, BigDecimal.ROUND_HALF_UP);

            // 生成随机电影数量 (50-150)
            int totalMovies = random.nextInt(101) + 50;

            MovieTypeAvg data = new MovieTypeAvg(typeId, avgScore, totalMovies);
            list.add(data);

            log.info("生成类型数据: ID={}, 类型={}, 平均分={}, 电影总数={}",
                    typeId, typeName, avgScore, totalMovies);
        }

        return list;
    }

    /**
     * 将生成的数据保存到数据库
     */
    private static void saveToDatabase(List<MovieTypeAvg> dataList) {
        String sql = "INSERT INTO movie_type_avg (type_id, avg_score, total_movies) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            for (MovieTypeAvg data : dataList) {
                pstmt.setInt(1, data.getTypeId());
                pstmt.setBigDecimal(2, data.getAvgScore());
                pstmt.setInt(3, data.getTotalMovies());
                pstmt.addBatch();
            }

            pstmt.executeBatch();
            conn.commit();
            log.info("成功插入 {} 条记录", dataList.size());

        } catch (SQLException e) {
            log.error("数据库操作异常", e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 初始化电影类型表（确保movie_types表包含基础数据）
     */
    private static void initMovieTypesTable(Connection conn) throws SQLException {
        // 检查表是否存在
        if (!tableExists(conn, "movie_types")) {
            log.info("创建movie_types表");
            createMovieTypesTable(conn);
        }

        // 检查movie_types表是否为空
        if (isMovieTypesTableEmpty(conn)) {
            log.info("movie_types表为空，插入基础数据");
            insertMovieTypes(conn);
        } else {
            log.info("movie_types表已有数据，跳过初始化");
        }
    }

    /**
     * 检查表是否存在
     */
    private static boolean tableExists(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        try (ResultSet resultSet = metaData.getTables(null, null, tableName, null)) {
            return resultSet.next();
        }
    }

    /**
     * 创建movie_types表
     */
    private static void createMovieTypesTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS movie_types (" +
                "type_id INT PRIMARY KEY, " +
                "type_name VARCHAR(50) NOT NULL UNIQUE, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }

    /**
     * 检查movie_types表是否为空
     */
    private static boolean isMovieTypesTableEmpty(Connection conn) throws SQLException {
        String sql = "SELECT COUNT(*) FROM movie_types";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt(1) == 0;
            }
            return true;
        }
    }

    /**
     * 向movie_types表插入基础数据
     */
    private static void insertMovieTypes(Connection conn) throws SQLException {
        String[] movieTypes = {
                "动作", "喜剧", "爱情", "科幻", "悬疑",
                "惊悚", "犯罪", "动画", "奇幻", "冒险"
        };

        String sql = "INSERT INTO movie_types (type_id, type_name) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);

            for (int i = 0; i < movieTypes.length; i++) {
                pstmt.setInt(1, i + 1); // type_id 从1开始
                pstmt.setString(2, movieTypes[i]);
                pstmt.executeUpdate();
            }

            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }
}